GO
/****** Object:  StoredProcedure [dbo].[usp_make_select_procedure]    Script Date: 2/2/2022 3:08:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Ryan P
-- Create date: 9/10/16
-- Modified:  2/13/18
-- Description:	Build a stored procedure to select
--		all matching rows from a table 
-- =============================================
CREATE OR ALTER PROCEDURE [dbo].[usp_make_select_procedure]
	@TABLE_NAME as nvarchar(100),
	@COLUMN as nvarchar(100),
	@TYPE as nvarchar(100)
AS
BEGIN

DECLARE
	@SQL_STATEMENT nvarchar(MAX)

SELECT 
@SQL_STATEMENT = 
'CREATE OR ALTER PROCEDURE [dbo].[usp_'+REPLACE(@TABLE_NAME,'tab_','')+'_select]
	@'+RTRIM(UPPER(@COLUMN))+' as '+@TYPE+'
AS
/******************************************************************************
* Description: Grab all results from '+@TABLE_NAME+' matching '+@COLUMN+'	   	
*			
* Procedure Test: 
	
	EXEC usp_'+REPLACE(@TABLE_NAME,'tab_','')+'_select @'+RTRIM(UPPER(@COLUMN))+'

* Change History:
* -----------------------------------------------------------------------------
* Date			|Author				|Reason
* -----------------------------------------------------------------------------
* '+CAST(CAST(GETDATE() AS date) AS nvarchar)+'	'+ORIGINAL_LOGIN()+'		Initial Release
*******************************************************************************/
BEGIN
SELECT
	*
FROM
	'+RTRIM(@TABLE_NAME)+'
WHERE
	'+RTRIM(@COLUMN)+' = @'+RTRIM(UPPER(@COLUMN))+'
END'

EXEC sp_executesql @SQL_STATEMENT 
	
SELECT
	'usp_'+REPLACE(@TABLE_NAME,'tab_','')+'_select created' AS message


-- This Template Created by Ryan Parish for Launch_Code, ©2016-2022.
-- It can be used for any purpose.
END

GO

/****** Object:  StoredProcedure [dbo].[usp_make_insert_procedure]    Script Date: 2/2/2022 3:28:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Ryan P
-- Create date: 9/10/16
-- Description:	Build a stored procedure to insert rows into a table.
--		This is just an example, use usp_make_update_procedure instead.  
-- =============================================
CREATE OR ALTER PROCEDURE [dbo].[usp_make_insert_procedure]
	@TABLE_NAME as nvarchar(100)
AS
BEGIN

DECLARE 
	@DECLARE_LIST nvarchar(MAX)=NULL,
	@VARIABLE_LIST nvarchar(MAX),
	@COLUMN_LIST nvarchar(MAX),
	@SQL_STATEMENT nvarchar(MAX)


SELECT 
	@DECLARE_LIST = COALESCE(@DECLARE_LIST+',
	','')+'	@'+UPPER(COLUMN_NAME)+' '+DATA_TYPE
	+ CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN '('+CAST(CHARACTER_MAXIMUM_LENGTH AS nvarchar)+')' ELSE '' END,
	@VARIABLE_LIST = COALESCE(@VARIABLE_LIST+',
	','')+'	@'+UPPER(COLUMN_NAME),
	@COLUMN_LIST = COALESCE(@COLUMN_LIST+',
		','')+COLUMN_NAME
FROM
	INFORMATION_SCHEMA.columns 
WHERE
	@TABLE_NAME = INFORMATION_SCHEMA.columns.TABLE_NAME 
ORDER BY
	ORDINAL_POSITION

SELECT 
@SQL_STATEMENT = 
'
CREATE OR ALTER PROCEDURE [dbo].[usp_'+REPLACE(@TABLE_NAME,'tab_','')+'_insert]
	'+@DECLARE_LIST+'
AS
/******************************************************************************
* Description: insert a row into '+@TABLE_NAME+'
*			
* Procedure Test: 
	
	EXEC usp_'+REPLACE(@TABLE_NAME,'tab_','')+'_insert '+@COLUMN_LIST+'

* Change History:
* -----------------------------------------------------------------------------
* Date			|Author				|Reason
* -----------------------------------------------------------------------------
* '+CAST(CAST(GETDATE() AS date) AS nvarchar)+'	'+ORIGINAL_LOGIN()+'		Initial Release
*******************************************************************************/
BEGIN
INSERT INTO 
	'+@TABLE_NAME+'(
		'+@COLUMN_LIST+'
	)
VALUES(
	'+@VARIABLE_LIST+'
)
END'

EXEC sp_executesql @SQL_STATEMENT 

SELECT
	'usp_'+REPLACE(@TABLE_NAME,'tab_','')+'_insert created' AS message

-- This Template Created by Ryan Parish for CoderGirl, ©2016-2022.
-- You may use it for any purpose.  
END

GO
/****** Object:  StoredProcedure [dbo].[usp_make_update_procedure]    Script Date: 2/2/2022 4:29:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:           Ryan P
-- Create date: 9/10/16
-- Modified:  2/13/18
-- Description: Dynamically build a stored procedure to update or insert 
--              a row into a table
-- =============================================
CREATE OR ALTER PROCEDURE [dbo].[usp_make_update_procedure]
	@TABLE_NAME as nvarchar(100)
AS
BEGIN
 
DECLARE
	@DECLARE_LIST nvarchar(MAX)=NULL,
	@VARIABLE_LIST nvarchar(MAX),
	@COLUMN_LIST nvarchar(MAX),
	@UPDATE_LIST nvarchar(MAX),
	@PK_LIST nvarchar(MAX),--primary key(s)
	@SQL_STATEMENT nvarchar(MAX),
	@SCHEMA_NAME nvarchar(255) = '',
	@SCHEMA_ID int

--check to make sure schema is not included
IF @TABLE_NAME like '%.%' BEGIN
	SELECT 'This Sproc only accepts tables:  it will look up the schema automatically' as message
	RETURN -1
END 
	
--check if there is an associated schema
SELECT 
	@SCHEMA_ID = CASE WHEN MIN(sys.schemas.schema_id) <> MAX(sys.schemas.schema_id) THEN -1 
					ELSE MIN(sys.schemas.schema_id) END,
	@SCHEMA_NAME = COALESCE(min(sys.schemas.name)+'.','')
FROM
	sys.tables 
	LEFT JOIN sys.schemas ON sys.schemas.schema_id = sys.tables.schema_id
WHERE
	sys.tables.name = @TABLE_NAME
	
IF @SCHEMA_ID = -1 BEGIN
	SELECT @SCHEMA_NAME + ' was not the only schema found matching this table name!  Aborting!' as message
	RETURN -1
END 
 
SELECT
	@DECLARE_LIST = COALESCE(@DECLARE_LIST+',
	','')+'@'+UPPER(sys.columns.name)+' '+sys.types.name
	+ CASE WHEN sys.types.collation_name IS NOT NULL AND is_user_defined= 0 THEN '('
	+ CASE WHEN sys.columns.max_length = -1 THEN 'MAX' ELSE CAST(sys.columns.max_length AS nvarchar) END  --figure out size of char() type
	+')' ELSE '' END,
	--ignore columns with identity specification:  they will be automatically created
	@VARIABLE_LIST = CASE WHEN sys.columns.is_identity = 1 THEN @VARIABLE_LIST
		ELSE COALESCE(@VARIABLE_LIST+',
		','')+'@'+UPPER(sys.columns.name) END,	   
	@COLUMN_LIST = CASE WHEN sys.columns.is_identity = 1 THEN @COLUMN_LIST
			ELSE COALESCE(@COLUMN_LIST+',
		','')+sys.columns.name END,
	@UPDATE_LIST = CASE WHEN key_table.COLUMN_NAME IS NULL --skip primary keys for update
					THEN COALESCE(@UPDATE_LIST+',
		','')+sys.columns.name+'='+'@'+UPPER(sys.columns.name)
			ELSE @UPDATE_LIST END,
	@PK_LIST = CASE WHEN key_table.COLUMN_NAME IS NULL THEN @PK_LIST--only include primary keys
					ELSE COALESCE(@PK_LIST+' AND ','')+'@'+UPPER(sys.columns.name)+'='+sys.columns.name
				END
FROM
       sys.tables INNER JOIN sys.columns
              ON sys.tables.object_id=sys.columns.object_id
       INNER JOIN sys.types
              ON sys.types.user_type_id=sys.columns.user_type_id
	   LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tab_constraints
	          ON tab_constraints.TABLE_NAME = sys.tables.name 
			  AND tab_constraints.CONSTRAINT_TYPE = 'PRIMARY KEY'
       LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE as key_table
              ON key_table.TABLE_NAME = sys.tables.name
              AND key_table.COLUMN_NAME = sys.columns.name
			  AND tab_constraints.CONSTRAINT_NAME = key_table.CONSTRAINT_NAME	   
WHERE
       @TABLE_NAME = sys.tables.name
	   AND is_computed = 0 --don't touch computed columns at all
ORDER BY
       column_id
 
 
SELECT
       @SQL_STATEMENT =
'
-- =============================================
CREATE OR ALTER PROCEDURE '+@SCHEMA_NAME+'[usp_'+REPLACE(@TABLE_NAME,'tab_','')+'_update]
	'+@DECLARE_LIST+'
AS
/******************************************************************************
* Description: insert or update a row into '+@TABLE_NAME+'
*			
* Procedure Test: 
	
	EXEC usp_'+REPLACE(@TABLE_NAME,'tab_','')+'_update '+@COLUMN_LIST+'

* Change History:
* -----------------------------------------------------------------------------
* Date			|Author				|Reason
* -----------------------------------------------------------------------------
* '+CAST(CAST(GETDATE() AS date) AS nvarchar)+'	'+ORIGINAL_LOGIN()+'		Initial Release
*******************************************************************************/
BEGIN
 
IF (SELECT COUNT(*) FROM '+@SCHEMA_NAME+@TABLE_NAME+' WHERE '+@PK_LIST+') = 0 BEGIN
	--create new row if there is no matching row
	INSERT INTO	'+@SCHEMA_NAME+''+@TABLE_NAME+'(
		'+@COLUMN_LIST+'
	)
	VALUES(
		'+@VARIABLE_LIST+'
	)	
	SELECT ''INSERT SUCCESSFUL'' AS message--always good to return something on success
END
ELSE BEGIN--update existing row
	UPDATE
		'+@SCHEMA_NAME+''+@TABLE_NAME+'
	SET
		'+@UPDATE_LIST+'
	WHERE
		'+@PK_LIST+'
	
	SELECT ''UPDATE SUCCESSFUL'' AS message
END 
--end of sproc
END'
/* for debugging */
SELECT
       @SQL_STATEMENT AS SQL_statement,
       @DECLARE_LIST as DECLARE_LIST,
       @VARIABLE_LIST as VARIABLE_LIST,
       @COLUMN_LIST as COLUMN_LIST,
       @UPDATE_LIST as update_list,
       @PK_LIST as pk
 
IF @SQL_STATEMENT IS NOT NULL BEGIN
       EXEC sp_executesql @SQL_STATEMENT
      
       SELECT
              'usp_'+REPLACE(@TABLE_NAME,'tab_','')+'_update created' AS message
END
ELSE BEGIN
	IF @DECLARE_LIST IS NULL AND @PK_LIST IS NULL BEGIN
		SELECT 
			'Error:  '+@TABLE_NAME+  ' has no columns or does not exist.' AS message
		RETURN 
	END
	IF @PK_LIST IS NULL BEGIN
		SELECT
			'Error:  '+@TABLE_NAME+  ' has no primary key.' AS message
		RETURN 
    END
	ELSE BEGIN
		SELECT
			'Error making usp_'+REPLACE(@TABLE_NAME,'tab_','')+'_update.' AS message
		RETURN 
	END
END
 
-- This Template Created by Ryan Parish for coder_girl, ©2016-2022.
-- It can be used for any purpose.
END